package base;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.Vector;

import javax.naming.Context;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.sql.DataSource;

import org.apache.log4j.Logger;

/**
 * @author qsj
 * 
 */
public class Sql {
	private Connection conn = null;
	private Vector<Object> statements = new Vector<Object>();
	private Vector<Object> conns = new Vector<Object>();
	ResultSet rs;
	Context ctx;
	public static final int TimeOut = 5000;
	private static String host = "localhost";
	private static String dbName = "public";
	private static final String userName = "root";
	private static final String passWord = "1";
	static Logger logger = Logger.getLogger(Sql.class);

	public Sql() {
		try {
			ctx = new InitialContext();
		} catch (NamingException e) {
		}
	}

	/* 直接连接 */
	public Connection getConnection() throws Exception {

		if (conn == null || conn.isClosed()) {
			Class.forName("com.mysql.jdbc.Driver").newInstance();
			conn = DriverManager.getConnection("jdbc:mysql://" + host + "/"
					+ dbName + "?user=" + userName + "&password=" + passWord);
			conns.addElement(conn);
		}
		return conn;

	}

	public Connection getConnectionByPool() throws Exception {
		if (conn == null) {
			conn = ((DataSource) ctx.lookup("java:comp/env/jdbc/public"))
					.getConnection();
			conns.addElement(conn);
		}
		return conn;

	}

	/***************************************************************************
	 * �ͷ����д�����l��
	 **************************************************************************/

	private void freeConns() {
		for (int i = 0; i < conns.size(); i++) {
			try {
				Connection conn = (Connection) conns.elementAt(i);
				if (conn != null) {
					conn.close();
					conn = null;
				}
			} catch (Exception e) {
			}
		}
		conns.removeAllElements();
	}

	/**
	 * �ͷ�����Statement
	 * 
	 * @throws Exception
	 */
	private void freestatement() {
		for (int i = 0, s = statements.size(); i < s; i++) {
			try {
				Statement st = (Statement) statements.elementAt(i);
				if (st != null) {
					st.close();
					st = null;
				}
			} catch (Exception e) {
			}
		}
		statements.removeAllElements();
	}

	public ArrayList<HashMap<String, String>> getListFromSqlByPool(String sql)
			throws Exception {
		ArrayList<HashMap<String, String>> list = new ArrayList<HashMap<String, String>>();
		Date d1 = new Date();
		System.out.println(sql);
		ResultSet myrs = getStatementByPool().executeQuery((sql));
		Date d2 = new Date();
		long timenum = d2.getTime() - d1.getTime();
		if (timenum > TimeOut) {
			log(timenum + "    " + (sql));
		}

		ResultSetMetaData d = myrs.getMetaData();
		int colCount = 1;
		colCount = d.getColumnCount();
		while (myrs.next()) {
			java.util.HashMap<String, String> colMap = new java.util.HashMap<String, String>();
			for (int i = 1; i <= colCount; i++) {
				colMap.put(d.getColumnName(i).toUpperCase(),
						(myrs.getString(i) == null) ? "" : (myrs.getString(i)
								.replaceAll(" ", " ")));
			}
			list.add(colMap);
		}
		myrs.close();
		return list;
	}

	public ArrayList<HashMap<String, String>> getListFromSql(String sql)
			throws Exception {
		ArrayList<HashMap<String, String>> list = new ArrayList<HashMap<String, String>>();
		Date d1 = new Date();
		System.out.println(sql);
		ResultSet myrs = getStatement().executeQuery((sql));
		Date d2 = new Date();
		long timenum = d2.getTime() - d1.getTime();
		if (timenum > TimeOut) {
			log(timenum + "    " + (sql));
		}

		ResultSetMetaData d = myrs.getMetaData();
		int colCount = 1;
		colCount = d.getColumnCount();
		while (myrs.next()) {
			java.util.HashMap<String, String> colMap = new java.util.HashMap<String, String>();
			for (int i = 1; i <= colCount; i++) {
				colMap.put(d.getColumnName(i).toUpperCase(),
						(myrs.getString(i) == null) ? "" : (myrs.getString(i)
								.replaceAll(" ", " ")));
			}
			list.add(colMap);
		}
		myrs.close();
		return list;
	}

	public HashMap<String, String> getMapFromSqlByPool(String sql)
			throws Exception {
		java.util.HashMap<String, String> colMap = new java.util.HashMap<String, String>();
		Date d1 = new Date();
		ResultSet myrs = getStatementByPool().executeQuery((sql));
		System.out.println("SQL:" + sql);
		Date d2 = new Date();
		long timenum = d2.getTime() - d1.getTime();
		if (timenum > TimeOut) {
			log(timenum + "    " + (sql));
		}

		ResultSetMetaData d = myrs.getMetaData();
		int colCount = 1;
		colCount = d.getColumnCount();
		if (myrs.next()) {
			for (int i = 1; i <= colCount; i++) {
				colMap.put(d.getColumnName(i).toUpperCase(),
						(myrs.getString(i) == null) ? "" : (myrs.getString(i)));
			}
		}
		myrs.close();
		return colMap;
	}

	public void getRSFromSqlByPool(String sql) throws Exception {
		if (this.rs != null)
			this.rs.close();
		this.rs = getStatementByPool().executeQuery((sql));
	}

	public void getRSFromSql(String sql) throws Exception {
		if (this.rs != null)
			this.rs.close();
		this.rs = getStatement().executeQuery((sql));
	}

	// ���ؽ��
	public ResultSet getResultSet() {
		return this.rs;
	}

	public boolean getRsNext() throws SQLException {
		return this.rs.next();
	}

	public String getRsString(String fieldName) throws SQLException {
		return this.rs.getString(fieldName);
	}

	public String getRsString(int fielInt) throws SQLException {
		return this.rs.getString(fielInt);
	}

	public int getRsInt(String fieldName) throws SQLException {
		return this.rs.getInt(fieldName);
	}

	public int getRsInt(int fielInt) throws SQLException {
		return this.rs.getInt(fielInt);
	}

	public float getRsFloat(String fieldName) throws SQLException {
		return this.rs.getFloat(fieldName);
	}

	public float getRsFloat(int fielInt) throws SQLException {
		return this.rs.getFloat(fielInt);
	}

	public int executeUpdateByPool(String sql) throws Exception {
		Date d1 = new Date();
		System.out.println((sql));
		int reInt = getStatementByPool().executeUpdate((sql));
		Date d2 = new Date();
		long timenum = d2.getTime() - d1.getTime();
		if (timenum > TimeOut) {
			log(timenum + "    " + (sql));
		}
		return reInt;
	}

	public int executeUpdate(String sql) throws Exception {
		Date d1 = new Date();
		System.out.println((sql));
		int reInt = getStatement().executeUpdate((sql));
		Date d2 = new Date();
		long timenum = d2.getTime() - d1.getTime();
		if (timenum > TimeOut) {
			log(timenum + "    " + (sql));
		}
		return reInt;
	}

	public Statement getStatementByPool() throws Exception {
		Statement stmt;
		getConnectionByPool();

		stmt = conn.createStatement();
		statements.addElement(stmt);
		return stmt;
	}

	public Statement getStatement() throws Exception {
		Statement stmt;
		getConnection();

		stmt = conn.createStatement();
		statements.addElement(stmt);
		return stmt;
	}

	public void close() {
		try {
			if (this.rs != null)
				this.rs.close();
		} catch (Exception e) {
			logger.info("sql.error==" + e.toString());
		} finally {
			freestatement();
			freeConns();
			try {
				if (ctx != null)
					ctx.close();
			} catch (Exception e) {
			}
			StackTraceElement element = new Exception().getStackTrace()[1];
			logger.info("close.filename==" + element.getFileName());
		}
	}

	public int getStatNum() {
		return statements.size();
	}

	public Connection getNowConn() {
		return conn;
	}

	private void log(String content) throws Exception {
	}
}
